--drop table if exists dongfeng.navinfo_stage_a203;
--创建外部表
create external table if not exists dongfeng.navinfo_stage_a203 (
             latitude                    int      comment       '纬度',
             longitude                   int      comment       '经度',
             originallat                 int      comment       '原始纬度',
             originallng                 int      comment       '原始经度',
             height                      int      comment       '海拔',
             direction                   int      comment       '方向',
             gpsdate                     bigint   comment       '时间',
             faultstatus                 int      comment       '故障状态0:非激活1:激活',
             faultaddress                int      comment       '故障源地址',
             faultcontent                bigint   comment       '故障内容',
             totaldrivemileage           bigint   comment       '总行驶里程',
             gpsspeed                    int      comment       'GPS速度',
             sourceaddresscnt            int      comment       '源地址个数',
             sourceaddress               int      comment       '源地址',
             collectdate                 bigint   comment       '采集时间',
             datalength                  int      comment       '后续数据长度',
             atmosphericpressure         double   comment       '大气压力',
             atmospherictemperature      double   comment       '环境温度',
             fakethrottleopening         double   comment       'VECU伪油门开度',
             t50status                   int      comment       'T50状态',
             throttleopening             double   comment       '加速踏板位置',
             dashboardspeed              double   comment       '仪表车速',
             totalmileage                double   comment       '累计里程',
             subtotalmileage             double   comment       '小计里程',
             intakeairtemperature        double   comment       'SCR进口温度',
             exhaustgastemperature       double   comment       'SCR出口温度',
             noxincontr                  double   comment       '进口NOx浓度',
             dpfpressurediff             double   comment       'DPF压差',
             noxoutcontr                 double   comment       '出口NOx浓度',
             urealevel                   double   comment       '尿素液位',
             ureatemp                    double   comment       '尿素罐内尿素温度',
             refertorque                 int      comment       '发动机参考扭矩',
             egrstatus                   int      comment       'EGR阀状态',
             actualoutputtorque          double   comment       '发动机实际扭矩百分比',
             enginespeed                 double   comment       '发动机转速',
             outflow                     double   comment       '排气流量',
             innox                       int      comment       '进口NOx露点释放',
             outnox                      int      comment       '出口NOx露点释放',
             inflow                      double   comment       '发动机进气流量',
             watertemperature            int      comment       '发动机冷却液温度',
             currentgear                 int      comment       'AMT变速箱当前档位',
             intakemanifoldpressure      int      comment       '发动机进气歧管压力',
             intakemanifoldtemperature   int      comment       '发动机进气歧管温度',
             outtakeairtemperature       double   comment       '排气温度',
             realtimeoilconsumption      double   comment       '发动机瞬时油耗(km/L)',
             averagefuelconsumption      double   comment       '发动机平均油耗(km/L)',
             throttle1valve              double   comment       '节气门1位置',
             eecugear                    int      comment       'EECU计算变速箱档位',
             oilpressure                 int      comment       '发动机机油压力',
             terminalid                  bigint   comment       '终端号',
             part_time                                 INT           COMMENT '分区时间'
) row format delimited fields terminated by '\001' STORED AS TEXTFILE location '/user/data/dongfeng/stage_import_a203';

--创建目标表
create table if not exists dongfeng.navinfo_parquet_a203(
              latitude                    int      comment       '纬度',
              longitude                   int      comment       '经度',
              originallat                 int      comment       '原始纬度',
              originallng                 int      comment       '原始经度',
              height                      int      comment       '海拔',
              direction                   int      comment       '方向',
              gpsdate                     bigint   comment       '时间',
              faultstatus                 int      comment       '故障状态0:非激活1:激活',
              faultaddress                int      comment       '故障源地址',
              faultcontent                bigint   comment       '故障内容',
              totaldrivemileage           bigint   comment       '总行驶里程',
              gpsspeed                    int      comment       'GPS速度',
              sourceaddresscnt            int      comment       '源地址个数',
              sourceaddress               int      comment       '源地址',
              collectdate                 bigint   comment       '采集时间',
              datalength                  int      comment       '后续数据长度',
              atmosphericpressure         double   comment       '大气压力',
              atmospherictemperature      double   comment       '环境温度',
              fakethrottleopening         double   comment       'VECU伪油门开度',
              t50status                   int      comment       'T50状态',
              throttleopening             double   comment       '加速踏板位置',
              dashboardspeed              double   comment       '仪表车速',
              totalmileage                double   comment       '累计里程',
              subtotalmileage             double   comment       '小计里程',
              intakeairtemperature        double   comment       'SCR进口温度',
              exhaustgastemperature       double   comment       'SCR出口温度',
              noxincontr                  double   comment       '进口NOx浓度',
              dpfpressurediff             double   comment       'DPF压差',
              noxoutcontr                 double   comment       '出口NOx浓度',
              urealevel                   double   comment       '尿素液位',
              ureatemp                    double   comment       '尿素罐内尿素温度',
              refertorque                 int      comment       '发动机参考扭矩',
              egrstatus                   int      comment       'EGR阀状态',
              actualoutputtorque          double   comment       '发动机实际扭矩百分比',
              enginespeed                 double   comment       '发动机转速',
              outflow                     double   comment       '排气流量',
              innox                       int      comment       '进口NOx露点释放',
              outnox                      int      comment       '出口NOx露点释放',
              inflow                      double   comment       '发动机进气流量',
              watertemperature            int      comment       '发动机冷却液温度',
              currentgear                 int      comment       'AMT变速箱当前档位',
              intakemanifoldpressure      int      comment       '发动机进气歧管压力',
              intakemanifoldtemperature   int      comment       '发动机进气歧管温度',
              outtakeairtemperature       double   comment       '排气温度',
              realtimeoilconsumption      double   comment       '发动机瞬时油耗(km/L)',
              averagefuelconsumption      double   comment       '发动机平均油耗(km/L)',
              throttle1valve              double   comment       '节气门1位置',
              eecugear                    int      comment       'EECU计算变速箱档位',
              oilpressure                 int      comment       '发动机机油压力',
              terminalid                  bigint   comment       '终端号',
              create_time                     STRING              COMMENT '数据创建时间'
) partitioned by (part_time int) stored as Parquet TBLPROPERTIES('parquet.compression'='SNAPPY');
